Data transfer between API (Application Programming Interface) and databases is a frequently used method in many applications. This process ensures that the data presented by the API is saved in the database or the data retrieved from the database is used by the API. Here, after setting up the necessary configurations, we will talk about how to transfer the JSON response to the database. Before starting the process, make sure that you have created the table according to the variables in the JSON file.
First of all, we drag and drop a Data Flow Task in our newly opened Package.
We place the Script Component in it which is located in the Common tab in the SSIS Toolbox. We click on Script Component and we will mark the Source on the screen that appears.
Right click on Script Component and click on Show Advance Editor. Click on the Input & Output Properties section, in the section that appears, we add all the columns in the JSON file to the Output Columns, do not forget to edit the data type section in each variable.
After completing the process, click Edit from Script Component.
We need to add reference from Solution Explorer, and for this we click add references from reference.
We add system.web.extensions. You can find it quickly by searching.
Now, we will deserialize our data. For this we need to add Class. We give a name by selecting the add new item option from the Properties, then the Class.
This will add an empty class to our script, here we write our columns in C# language. First we write our data type, then the variable name, then the parameters in square brackets. Don't worry, you can do this easily even if you don't know C#. As an example, we leave a piece of code here.
After the process is complete, we return to the main.cs script, the piece of code shown below will be pasted in the section starting with public override void.
In your own cases,
Do not forget to write the names of your columns in the section starting with Output0Buffer.
Write your reference in the section starting with List.
Give the Path of the Json file in the line starting with File.ReadAllText
Some classes may not be readable, you will need to define for this, move your mouse to the marked areas and click Show potential fixes, select the 1st suggestions.
When you hover over it, you will receive a notification like this.
Then you can use suggestion 1 as in the image below.
Possible necessary definitions are highlighted. After it is defined, it will be written in color as on the screen.
Exit the script, click Ok to save the changes.
Drag and drop OLE DB Destination, then make the necessary configurations and connection.
You can see the OLE DB Destination connection configurations below:
After the process is complete, run your Package.
